0
521
ViewsHow to migrate [DDL] from generation type identity to generation type sequence for postgres db
As of now I am generating id using
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
the sequence generated in DB is like:
CREATE SEQUENCE public.table_name_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE public.table_name_id_seq OWNED BY public.table_name.id;
ALTER TABLE ONLY public.table_name ALTER COLUMN id SET DEFAULT``nextval('public.table_name_id_seq'::regclass);
or at some places in definition itself:
CREATE TABLE "public.table_name" (
"id" int8 NOT NULL DEFAULT nextval('table_name_id_seq'::regclass),
"some_column" varchar(255),
PRIMARY KEY ("id")
);
I am using hibernate as JPA provider, I needed to enable insert and update batching due to which I want to change the generation type to SEQUENCE from IDENTITY.
After changing to generation type sequence [in hibernate] and using older sequence [of postgres] i see weird id being generated. This is not the expected behavior.
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "table_name_id_seq ")
private Long id;
The ids which are highlighted in yellow is created after using generation type sequence. I was expecting id after 23 to be 24 and not -22.
How can I fix this? How can I smoothly shift from IDENTITY to SEQUENCE? What is the proper DDL?
1 answers
Answer question0
Answering to this part of your question:
How can I smoothly shift from
IDENTITYtoSEQUENCE? What is the proper DDL?
You should do the following things:
- Drop the default value from the
table_name.idcolumn:
alter table only table_name
alter column id drop default;
- Correct your mapping for the
idfield:
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "my_generator")
@SequenceGenerator(name = "my_generator", sequenceName = "table_name_id_seq", allocationSize = 1)
private Long id;
Please note that an allocationSize should be equal to the INCREMENT BY of your sequence definition.